<?php
/**
 * @description : model of db.
 * @author : stephen.mo
 * @date : 2010-06-10
 */

class ModelDailySummary extends model {

    static public function getInstance() {
        static $instance = null ;
        return $instance ? ($instance) : ($instance = new ModelDailySummary()) ;
    }

    static public function getTableName() {
        return 'video_access_log_temp';
    }

    static public function getTablePrefixName($year_month) {
        if (empty($year_month)) $year_month = date('Y_m');
        return 'video_access_log_'.$year_month;
    }
    
    static public function getTableNameOfSubscription() {
        return 'subscription_log';
    }

    static public function getToday() {
        return date('Y-m-d');
    }

    public function getViewFrequencyData($date) {
        $table_name = self::getTableName();
        //the date more than one month,choose the month table.
        if (strtotime(self::getToday())-strtotime($date)>=30*24*60*60) {
            $current_date = date('Y_m',strtotime($date));
            $table_name = self::getTablePrefixName($current_date);
        }
        $query = "SELECT count(*) AS Total FROM ".$table_name." WHERE SUBSTRING(play_time,1,10)='".$date."' ";
        $data = Model::selectBySql($query);
        return (isset($data[0]['Total'])) ? $data[0]['Total'] : 0;
    }

    public function getTotalDurationWithBitrate($date) {
        $table_name = self::getTableName();
        //the date more than one month,choose the month table.
        if (strtotime(self::getToday())-strtotime($date)>=30*24*60*60) {
            $current_date = date('Y_m',strtotime($date));
            $table_name = self::getTablePrefixName($current_date);
        }

        $query = "SELECT bitrate,count(id) AS Total FROM ".$table_name.
                 " WHERE SUBSTRING(play_time,1,10)='".$date."' ".
                 " GROUP BY bitrate  ".
                 " ORDER BY bitrate ASC ";
        $data = Model::selectBySql($query);
        return (count($data)>0) ? $data : AC::$default_bitrate_null;
    }

    public function getUniqueUserData($date) {
        $table_name = self::getTableName();
        //the date more than one month,choose the month table.
        if (strtotime(self::getToday())-strtotime($date)>=30*24*60*60) {
            $current_date = date('Y_m',strtotime($date));
            $table_name = self::getTablePrefixName($current_date);
        }

        $query = "SELECT DISTINCT member_id FROM ".$table_name.
                 " WHERE SUBSTRING(play_time,1,10)='".$date."' ";
        $data = Model::selectBySql($query);
        return count($data);
    }

    public function getSubscriberPackageData($date) {
        $table_name = self::getTableNameOfSubscription();
        $query = "SELECT package_id,type FROM ".$table_name.
                 " WHERE SUBSTRING(order_datetime,1,10)='".$date."' ".
                 " GROUP BY order_id,package_id,package_version ";
        $data = Model::selectBySql($query);
        
        $result = array();
        foreach($data as $key=>$list ) {
            $package_id = $list['package_id'];
            $package_type = $list['type'];
            if (!isset($result[$package_id])) {
                $result[$package_id] = array();
                $result[$package_id]['total'] = 0;
                $result[$package_id]['new'] = 0;
                $result[$package_id]['renew'] = 0;
                $result[$package_id]['churn'] = 0;
            }
            $result[$package_id]['total'] ++;
            if ($package_type==1) {
                $result[$package_id]['new'] ++;
            }
            elseif($package_type==2) {
                $result[$package_id]['renew'] ++;
            }
            elseif($package_type==3) {
                $result[$package_id]['churn'] ++;
            }
        }
        
        return $result;
    }


}


?>